package com.gwsoft.mm.file;

import java.io.File;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.annotation.Resource;

import jxl.Sheet;
import jxl.Workbook;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Service;

import net.sf.json.JSONObject;

@Service("fileHandlerManage")
public class FileHandlerManagImple implements FileHandlerManage {

	@Resource(name="fileHandlerUtils")
	private FileHandlerUtils fileUtils;
	
	@Resource(name="ipInfoDao")
	private IpInfoDao ipDao;
	
	@Resource
	private ResourceDao resourceDao;
	
	@Resource(name="keyMessageDao")
	private KeyMessageResDao keyMesDao;
	
	@Resource(name="syncSongResService")
	private GetSongRes keySongResDao;
	private final static int LOOP_COUNT = 500; //每500条数据调用一次同步方法..
	
	public JSONObject categoryFileImport(ServiceProperties properties) {
		JSONObject json = new JSONObject();
		int rowCount = 0;
		Workbook book = null;
		try {
			String uploadFile = properties.getString("FILES");
			book = Workbook.getWorkbook(new File(uploadFile));
			Sheet sheet = book.getSheet(0);
						
			Map<String, Object> map = null;
			//抛弃第一行列名行
			for (int i=1;i<sheet.getRows();i++) {
				try {
					map = new HashMap<String, Object>();
					map.put("resId", sheet.getCell(0, i).getContents());					
					map.put("boxResId", sheet.getCell(1, i).getContents());
					
					if ((null != sheet.getCell(2, i)) && (!"".equals(sheet.getCell(2, i).getContents()))) {
						map.put("orderCode", sheet.getCell(2, i).getContents());
					}
					resourceDao.insertRingBoxWithOrder(map);
					rowCount = i;
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			book.close();
		} catch (Exception e) {
			json.put("error", "读取数据出错, 共处理数据"+rowCount+"条");
			e.printStackTrace();
		}
		json.put("rowcount", "共处理数据"+rowCount+"条");
		return json;
	}	

	public JSONObject categoryFileTemplate(ServiceProperties properties) {
		JSONObject json = new JSONObject();
		try {
			String[] titleLine = {"分类资源ID","下级资源ID","排序号"};
			String filePath = fileUtils.createFileTemplate(titleLine);
			
			if (StringUtils.isBlank(filePath)) {
				json.put("error", "生成模版文件失败");
			} else {
				json.put("filepath", filePath);
			}
		} catch (Exception e) {
			json.put("error", "生成模版文件失败");
			e.printStackTrace();
		}
		return json;
	}

	public JSONObject ipInfoImport(ServiceProperties properties) {
		JSONObject json = new JSONObject();
		int rowCount = 0;
		Workbook book = null;
		String noteIllegeIp = "";
		try {
			String uploadFile = properties.getString("FILES");
			book = Workbook.getWorkbook(new File(uploadFile));
			Sheet sheet = book.getSheet(0);
						
			ServiceProperties pro = null;
			//抛弃第一行列名行
			for (int i=1;i<sheet.getRows();i++) {
				try {
					pro = new ServiceProperties();
					String nettype = sheet.getCell(0, i).getContents().trim();
					if (nettype.equals("")) {
						continue;
					}
					pro.addProperty("NETTYPE", nettype);
					
					if ((null != sheet.getCell(1, i)) && (!"".equals(sheet.getCell(1, i).getContents()))) {
						String ipStart = sheet.getCell(1, i).getContents().trim();
						if (checkIp(ipStart)) {
							pro.addProperty("IP_START", ipStart);
						} else {
							noteIllegeIp += ipStart+".   ";
							System.out.println("this ip is not with correct format:"+ipStart+", So skip this line");
							continue;
						}						
					} else {
						pro.addProperty("IP_START", "");
					}
					
					if ((null != sheet.getCell(2, i)) && (!"".equals(sheet.getCell(2, i).getContents()))) {
						String ipEnd = sheet.getCell(2, i).getContents().trim();
						if (checkIp(ipEnd)) {
							pro.addProperty("IP_END", ipEnd);
						} else {
							noteIllegeIp += ipEnd+".   ";
							System.out.println("this ip is not with correct format:"+ipEnd+", So skip this line");
							continue;
						}						
					} else {
						pro.addProperty("IP_END", "");
					}
					
					if ((null != sheet.getCell(3, i)) && (!"".equals(sheet.getCell(3, i).getContents()))) {
						String ipStatic = sheet.getCell(3, i).getContents().trim();
						if (checkIp(ipStatic)) {
							pro.addProperty("IP_STATIC", ipStatic);
						} else {
							noteIllegeIp += ipStatic+".   ";
							System.out.println("this ip is not with correct format:"+ipStatic+", So skip this line");
							continue;
						}						
					} else {
						pro.addProperty("IP_STATIC", "");
					}
					
					if ((null != sheet.getCell(4, i)) && (!"".equals(sheet.getCell(4, i).getContents()))) {
						pro.addProperty("NOTE", sheet.getCell(4, i).getContents().trim());
					} else {
						pro.addProperty("NOTE", "");
					}
					
					if (checkNetTypeIp(pro)) {
						ipDao.insertIpInfo(pro);
						rowCount++;
					} else {
						System.out.println("This line is not match ctnet,ctwap rule, So skip it...");
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			book.close();
		} catch (Exception e) {
			json.put("error", "读取数据出错, 共处理数据"+rowCount+"条");
			e.printStackTrace();
		}
		if (StringUtils.isBlank(noteIllegeIp)) {
			json.put("rowcount", "共处理数据"+rowCount+"条");
		} else {
			json.put("rowcount", "共处理数据"+rowCount+"条，<Br>其中不合法IP："+noteIllegeIp);
		}		
		return json;
	}

	public JSONObject ipInfoTemplate(ServiceProperties properties) {
		JSONObject json = new JSONObject();
		try {
			String[] titleLine = {"网络类型","起始IP","结束IP","IP地址池","备注"};
			String filePath = fileUtils.createFileTemplate(titleLine);
			
			if (StringUtils.isBlank(filePath)) {
				json.put("error", "生成模版文件失败");
			} else {
				json.put("filepath", filePath);
			}
		} catch (Exception e) {
			json.put("error", "生成模版文件失败");
			e.printStackTrace();
		}
		return json;
	}
	
	
	//根据NETTYPE来检查IP地址，目前只检查CTNET和CTWAP
	public boolean checkNetTypeIp(ServiceProperties properties){
		String netType = properties.getString("NETTYPE").toUpperCase();
		boolean passPort = true;
		if (netType.equals("CTNET")) {
			if (!StringUtils.isBlank(properties.getString("IP_STATIC"))) {
				passPort = false;
			}
			
			if (!StringUtils.isBlank(properties.getString("IP_START")) && StringUtils.isBlank(properties.getString("IP_END"))) {
				passPort = false;
			}			
			if (!StringUtils.isBlank(properties.getString("IP_END")) && StringUtils.isBlank(properties.getString("IP_START"))) {
				passPort = false;
			}			
		} else if (netType.equals("CTWAP")) {
			if (!StringUtils.isBlank(properties.getString("IP_START")) || !StringUtils.isBlank(properties.getString("IP_END"))) {
				passPort = false;
			}
		}
		return passPort;
	}
	
	//检查IP地址合法性
	public boolean checkIp(String ip) {
		String regx = "\\d{1,3}.\\d{1,3}.\\d{1,3}.\\d{1,3}";
		Pattern p = Pattern.compile(regx);
		Matcher matcher = p.matcher(ip);
		if (matcher.matches()) {
			String[] ipArray = ip.split("\\.");
			boolean isNormal = true;
			for (String part : ipArray) {
				if (Integer.parseInt(part)>=0 && Integer.parseInt(part)<256) {
					continue;
				} else {
					isNormal =  false;
					break;
				}
			}
			return isNormal;
		} else {
			return false;
		}
	}

	public JSONObject keyMessageImport(ServiceProperties properties) {
		JSONObject json = new JSONObject();
		int rowCount = 0;
		Workbook book = null;
		try {
			String uploadFile = properties.getString("FILES");
			book = Workbook.getWorkbook(new File(uploadFile));
			Sheet sheet = book.getSheet(0);
						
			ServiceProperties insertPro = null;
			//抛弃第一行列名行
			for (int i=1;i<sheet.getRows();i++) {
				try {
					insertPro = new ServiceProperties();
					if ((null != sheet.getCell(0, i)) && (!"".equals(sheet.getCell(0, i).getContents()))) {
						insertPro.addProperty("key", sheet.getCell(0, i).getContents());
					} else {
						continue;
					}
					if ((null != sheet.getCell(1, i)) && (!"".equals(sheet.getCell(1, i).getContents()))) {
						insertPro.addProperty("message", sheet.getCell(1, i).getContents());
					} else {
						continue;
					}			
					keyMesDao.insertKeyMessage(insertPro);
					rowCount++;
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			book.close();
		} catch (Exception e) {
			json.put("error", "读取数据出错, 共处理数据"+rowCount+"条");
			e.printStackTrace();
		}
		json.put("rowcount", "共处理数据"+rowCount+"条");
		return json;
	}

	public JSONObject keyMessageTemplate(ServiceProperties properties) {
		JSONObject json = new JSONObject();
		try {
			String[] titleLine = {"关键字","消息内容"};
			String filePath = fileUtils.createFileTemplate(titleLine);
			
			if (StringUtils.isBlank(filePath)) {
				json.put("error", "生成模版文件失败");
			} else {
				json.put("filepath", filePath);
			}
		} catch (Exception e) {
			json.put("error", "生成模版文件失败");
			e.printStackTrace();
		}
		return json;
	}

	public JSONObject keyMessageResImport(ServiceProperties properties) {
		JSONObject json = new JSONObject();
		int rowCount = 0;
		Workbook book = null;
		try {
			String uploadFile = properties.getString("FILES");
			book = Workbook.getWorkbook(new File(uploadFile));
			Sheet sheet = book.getSheet(0);
						
			Map<String, Object> map = null;
			//抛弃第一行列名行
			for (int i=1;i<sheet.getRows();i++) {
				try {
					map = new HashMap<String, Object>();
					if ((null != sheet.getCell(0, i)) && (!"".equals(sheet.getCell(0, i).getContents()))) {
						map.put("keyid", sheet.getCell(0, i).getContents());
					} else {
						continue;
					}
					if ((null != sheet.getCell(1, i)) && (!"".equals(sheet.getCell(1, i).getContents()))) {
						map.put("resid", sheet.getCell(1, i).getContents());
					} else {
						continue;
					}	
					
					if ((null != sheet.getCell(2, i)) && (!"".equals(sheet.getCell(2, i).getContents()))) {
						map.put("order", sheet.getCell(2, i).getContents());
					}
					
					keyMesDao.insertKeyMessageRes(map);
					rowCount++;
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			book.close();
		} catch (Exception e) {
			json.put("error", "读取数据出错, 共处理数据"+rowCount+"条");
			e.printStackTrace();
		}
		json.put("rowcount", "共处理数据"+rowCount+"条");
		return json;
	}

	public JSONObject keyMessageResTemplate(ServiceProperties properties) {
		JSONObject json = new JSONObject();
		try {
			String[] titleLine = {"消息ID","资源ID","排序号"};
			String filePath = fileUtils.createFileTemplate(titleLine);
			
			if (StringUtils.isBlank(filePath)) {
				json.put("error", "生成模版文件失败");
			} else {
				json.put("filepath", filePath);
			}
		} catch (Exception e) {
			json.put("error", "生成模版文件失败");
			e.printStackTrace();
		}
		return json;
	}

	public JSONObject syncSongResByKeyImport(ServiceProperties properties) {
		JSONObject json = new JSONObject();
		int rowCount = 0;
		Workbook book = null;
		StringBuilder keyWords = new StringBuilder();
		
		try {
			String uploadFile = properties.getString("FILES");
			book = Workbook.getWorkbook(new File(uploadFile));
			Sheet sheet = book.getSheet(0);						
			//抛弃第一行列名行
			for (int i=1;i<sheet.getRows();i++) {
				try {
					if ((null != sheet.getCell(0, i)) && (!"".equals(sheet.getCell(0, i).getContents()))) {
						if (rowCount < LOOP_COUNT) {
							keyWords.append(";"+sheet.getCell(0, i).getContents());
							rowCount ++;
						} else {
							keySongResDao.doDealWithKeyWord(keyWords.toString());
							keyWords.delete(0, keyWords.length());
							
							rowCount = 0;
							keyWords.append(";"+sheet.getCell(0, i).getContents());
						}
					} else {
						continue;
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			
			keySongResDao.doDealWithKeyWord(keyWords.toString());
			book.close();
		} catch (Exception e) {
			json.put("error", "读取数据出错");
			e.printStackTrace();
		}
		json.put("rowcount", "系统会自动同步你录入的关键字， 请稍后查看");
		return json;
	}

	public JSONObject syncSongResByKeyTemplate(ServiceProperties properties) {
		JSONObject json = new JSONObject();
		try {
			String[] titleLine = {"关键字"};
			String filePath = fileUtils.createFileTemplate(titleLine);
			
			if (StringUtils.isBlank(filePath)) {
				json.put("error", "生成模版文件失败");
			} else {
				json.put("filepath", filePath);
			}
		} catch (Exception e) {
			json.put("error", "生成模版文件失败");
			e.printStackTrace();
		}
		return json;
	}
}
